"use strict";
var __createBinding = (this && this.__createBinding) || (Object.create ? (function(o, m, k, k2) {
    if (k2 === undefined) k2 = k;
    var desc = Object.getOwnPropertyDescriptor(m, k);
    if (!desc || ("get" in desc ? !m.__esModule : desc.writable || desc.configurable)) {
      desc = { enumerable: true, get: function() { return m[k]; } };
    }
    Object.defineProperty(o, k2, desc);
}) : (function(o, m, k, k2) {
    if (k2 === undefined) k2 = k;
    o[k2] = m[k];
}));
var __setModuleDefault = (this && this.__setModuleDefault) || (Object.create ? (function(o, v) {
    Object.defineProperty(o, "default", { enumerable: true, value: v });
}) : function(o, v) {
    o["default"] = v;
});
var __importStar = (this && this.__importStar) || (function () {
    var ownKeys = function(o) {
        ownKeys = Object.getOwnPropertyNames || function (o) {
            var ar = [];
            for (var k in o) if (Object.prototype.hasOwnProperty.call(o, k)) ar[ar.length] = k;
            return ar;
        };
        return ownKeys(o);
    };
    return function (mod) {
        if (mod && mod.__esModule) return mod;
        var result = {};
        if (mod != null) for (var k = ownKeys(mod), i = 0; i < k.length; i++) if (k[i] !== "default") __createBinding(result, mod, k[i]);
        __setModuleDefault(result, mod);
        return result;
    };
})();
Object.defineProperty(exports, "__esModule", { value: true });
exports.ComprehensiveFieldVerifier = void 0;
const excel_parser_server_js_1 = require("../lib/excel-parser.server.js");
const sqlite_database_js_1 = require("../lib/sqlite-database.js");
const fs = __importStar(require("fs"));
const path = __importStar(require("path"));
class ComprehensiveFieldVerifier {
    constructor() {
        this.results = [];
        this.specialCharacterAnalysis = [];
        this.db = new sqlite_database_js_1.SqliteDatabase();
    }
    /**
     * Main verification method - checks all Excel files against database tables
     */
    async verifyAllFieldMappings() {
        console.log('🔍 Starting comprehensive field verification...');
        try {
            // 1. Get all table mappings from database
            const tableMappings = await this.getTableMappings();
            console.log(`📊 Found ${tableMappings.length} table mappings`);
            // 2. Process each Excel file
            const excelFiles = this.getExcelFiles();
            console.log(`📁 Found ${excelFiles.length} Excel files`);
            // 3. Verify each mapping
            for (const excelFile of excelFiles) {
                console.log(`\n📄 Processing Excel file: ${excelFile}`);
                await this.verifyExcelFile(excelFile, tableMappings);
            }
            // 4. Generate summary
            const summary = this.generateSummary();
            // 5. Generate detailed report
            await this.generateDetailedReport();
            return {
                results: this.results,
                summary,
                specialCharacterAnalysis: this.specialCharacterAnalysis
            };
        }
        catch (error) {
            console.error('❌ Error during verification:', error);
            throw error;
        }
    }
    /**
     * Get all table mappings from database
     */
    async getTableMappings() {
        const mappings = [];
        try {
            // Get all table_c tables from database
            const tables = this.db.all(`
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name LIKE 'table_c%'
        ORDER BY name
      `);
            for (const table of tables) {
                const tableName = table.name;
                const tableCode = tableName.replace('table_c', 'C');
                // Try to get description from metadata
                const metadata = this.db.get(`
          SELECT description FROM parameter_table_metadata 
          WHERE table_name = ? OR table_code = ?
        `, [tableName, tableCode]);
                mappings.push({
                    excelSheetName: `C${tableName.replace('table_c', '')}`, // Convert table_c123 to C123
                    databaseTableName: tableName,
                    tableCode,
                    description: metadata?.description || `Table ${tableCode}`
                });
            }
            return mappings;
        }
        catch (error) {
            console.error('Error getting table mappings:', error);
            return [];
        }
    }
    /**
     * Get all available Excel files
     */
    getExcelFiles() {
        const excelDirs = [
            path.join(process.cwd(), 'excel', '06-06'),
            path.join(process.cwd(), 'excel', '06-18'),
            path.join(process.cwd(), 'excel', '06-21')
        ];
        const files = [];
        for (const dir of excelDirs) {
            if (fs.existsSync(dir)) {
                const dirFiles = fs.readdirSync(dir)
                    .filter(f => f.endsWith('.xlsx') || f.endsWith('.xls'))
                    .map(f => path.join(dir, f));
                files.push(...dirFiles);
            }
        }
        return files;
    }
    /**
     * Verify a single Excel file against database mappings
     */
    async verifyExcelFile(excelFilePath, tableMappings) {
        try {
            const parsedFile = (0, excel_parser_server_js_1.parseExcelFile_SERVER)(excelFilePath);
            console.log(`  📋 Parsed ${parsedFile.sheets.length} sheets`);
            for (const sheet of parsedFile.sheets) {
                // Find corresponding database table
                const mapping = tableMappings.find(m => m.excelSheetName === sheet.sheetName ||
                    sheet.sheetName.includes(m.excelSheetName) ||
                    m.excelSheetName.includes(sheet.sheetName));
                if (!mapping) {
                    console.log(`    ⚠️  No mapping found for sheet: ${sheet.sheetName}`);
                    continue;
                }
                console.log(`    🔗 Verifying sheet ${sheet.sheetName} -> ${mapping.databaseTableName}`);
                // Extract and verify fields
                const result = await this.verifyFieldMapping(sheet.sheetName, sheet.headers, mapping.databaseTableName, mapping.description);
                this.results.push(result);
                // Analyze special characters
                this.analyzeSpecialCharacters(sheet.headers);
            }
        }
        catch (error) {
            console.error(`Error processing Excel file ${excelFilePath}:`, error);
            this.results.push({
                tableName: path.basename(excelFilePath),
                status: 'ERROR',
                exactMatches: [],
                missingFields: [],
                extraFields: [],
                characterDifferences: [],
                totalExcelFields: 0,
                totalDatabaseFields: 0,
                matchPercentage: 0,
                errorMessage: error instanceof Error ? error.message : 'Unknown error'
            });
        }
    }
    /**
     * Verify field mapping between Excel headers and database columns
     */
    async verifyFieldMapping(sheetName, excelHeaders, databaseTableName, description) {
        try {
            // Get database column names
            const columns = this.db.all(`PRAGMA table_info(${databaseTableName})`);
            const dbColumns = columns
                .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name))
                .map(col => col.name);
            // Clean and normalize field names for comparison
            const cleanExcelHeaders = excelHeaders
                .filter(h => h && h.trim() !== '')
                .map(h => h.trim());
            const cleanDbColumns = dbColumns.map(col => col.trim());
            // Find exact matches
            const exactMatches = [];
            const missingFields = [];
            const extraFields = [];
            const characterDifferences = [];
            // Check each Excel header against database columns
            for (const excelHeader of cleanExcelHeaders) {
                const exactMatch = cleanDbColumns.find(dbCol => this.isExactMatch(excelHeader, dbCol));
                if (exactMatch) {
                    exactMatches.push(excelHeader);
                }
                else {
                    // Look for close matches with character differences
                    const closeMatch = cleanDbColumns.find(dbCol => this.isCloseMatch(excelHeader, dbCol));
                    if (closeMatch) {
                        const difference = this.findCharacterDifference(excelHeader, closeMatch);
                        characterDifferences.push({
                            excel: excelHeader,
                            database: closeMatch,
                            difference
                        });
                    }
                    else {
                        missingFields.push(excelHeader);
                    }
                }
            }
            // Check for extra fields in database
            for (const dbCol of cleanDbColumns) {
                const foundInExcel = cleanExcelHeaders.find(excelHeader => this.isExactMatch(excelHeader, dbCol) ||
                    this.isCloseMatch(excelHeader, dbCol));
                if (!foundInExcel) {
                    extraFields.push(dbCol);
                }
            }
            // Calculate match percentage
            const totalExcelFields = cleanExcelHeaders.length;
            const totalDatabaseFields = cleanDbColumns.length;
            const matchedFields = exactMatches.length;
            const matchPercentage = totalExcelFields > 0
                ? (matchedFields / totalExcelFields) * 100
                : 0;
            // Determine status
            let status = 'MATCH';
            if (missingFields.length > 0)
                status = 'MISSING_FIELDS';
            if (extraFields.length > 0)
                status = 'EXTRA_FIELDS';
            if (characterDifferences.length > 0)
                status = 'CHARACTER_DIFFERENCES';
            if (matchPercentage === 100 && missingFields.length === 0 && extraFields.length === 0 && characterDifferences.length === 0) {
                status = 'MATCH';
            }
            return {
                tableName: `${sheetName} -> ${databaseTableName}`,
                status,
                exactMatches,
                missingFields,
                extraFields,
                characterDifferences,
                totalExcelFields,
                totalDatabaseFields,
                matchPercentage: Math.round(matchPercentage * 100) / 100
            };
        }
        catch (error) {
            console.error(`Error verifying field mapping for ${databaseTableName}:`, error);
            return {
                tableName: `${sheetName} -> ${databaseTableName}`,
                status: 'ERROR',
                exactMatches: [],
                missingFields: [],
                extraFields: [],
                characterDifferences: [],
                totalExcelFields: 0,
                totalDatabaseFields: 0,
                matchPercentage: 0,
                errorMessage: error instanceof Error ? error.message : 'Unknown error'
            };
        }
    }
    /**
     * Check if two field names are exactly the same
     */
    isExactMatch(excel, database) {
        // Character-by-character comparison
        return excel === database;
    }
    /**
     * Check if two field names are close matches (allowing for minor differences)
     */
    isCloseMatch(excel, database) {
        // Remove common variations
        const normalizeField = (field) => {
            return field
                .replace(/\s+/g, '') // Remove all spaces
                .replace(/[_\-]/g, '') // Remove underscores and dashes
                .toLowerCase();
        };
        const normalizedExcel = normalizeField(excel);
        const normalizedDb = normalizeField(database);
        // Check if normalized versions match
        if (normalizedExcel === normalizedDb)
            return true;
        // Check for common Chinese punctuation variations
        const chineseVariations = [
            [/（/g, '('],
            [/）/g, ')'],
            [/／/g, '/'],
            [/。/g, '.'],
            [/，/g, ','],
            [/：/g, ':'],
            [/；/g, ';']
        ];
        let excelVariant = excel;
        let dbVariant = database;
        for (const [from, to] of chineseVariations) {
            excelVariant = excelVariant.replace(from, to);
            dbVariant = dbVariant.replace(from, to);
        }
        return excelVariant === dbVariant;
    }
    /**
     * Find the specific character difference between two field names
     */
    findCharacterDifference(excel, database) {
        const differences = [];
        // Character-by-character comparison
        const maxLength = Math.max(excel.length, database.length);
        for (let i = 0; i < maxLength; i++) {
            const excelChar = excel[i] || '';
            const dbChar = database[i] || '';
            if (excelChar !== dbChar) {
                differences.push(`Position ${i}: '${excelChar}' vs '${dbChar}'`);
            }
        }
        if (excel.length !== database.length) {
            differences.push(`Length difference: ${excel.length} vs ${database.length}`);
        }
        return differences.join('; ');
    }
    /**
     * Analyze special characters in field names
     */
    analyzeSpecialCharacters(headers) {
        for (const header of headers) {
            if (!header || header.trim() === '')
                continue;
            const analysis = {
                field: header,
                hasSlash: header.includes('/') || header.includes('／'),
                hasParentheses: header.includes('(') || header.includes(')') || header.includes('（') || header.includes('）'),
                hasSpecialPunctuation: /[，。：；！？""'']/g.test(header),
                isMultiLine: header.includes('\n') || header.includes('\r'),
                specialCharacters: []
            };
            // Extract all special characters
            const specialChars = header.match(/[^\w\s\u4e00-\u9fff]/g) || [];
            analysis.specialCharacters = [...new Set(specialChars)];
            // Add to analysis if it has special characteristics
            if (analysis.hasSlash || analysis.hasParentheses || analysis.hasSpecialPunctuation || analysis.isMultiLine) {
                this.specialCharacterAnalysis.push(analysis);
            }
        }
    }
    /**
     * Generate summary statistics
     */
    generateSummary() {
        const totalTables = this.results.length;
        const perfectMatches = this.results.filter(r => r.status === 'MATCH').length;
        const partialMatches = this.results.filter(r => r.status !== 'MATCH' && r.status !== 'ERROR' && r.matchPercentage > 0).length;
        const failures = this.results.filter(r => r.status === 'ERROR').length;
        const totalMatchPercentage = this.results.length > 0
            ? this.results.reduce((sum, r) => sum + r.matchPercentage, 0) / this.results.length
            : 0;
        return {
            totalTables,
            perfectMatches,
            partialMatches,
            failures,
            overallMatchPercentage: Math.round(totalMatchPercentage * 100) / 100
        };
    }
    /**
     * Generate detailed report
     */
    async generateDetailedReport() {
        const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
        const reportPath = path.join(process.cwd(), `field-verification-report-${timestamp}.json`);
        const report = {
            timestamp: new Date().toISOString(),
            summary: this.generateSummary(),
            results: this.results,
            specialCharacterAnalysis: this.specialCharacterAnalysis,
            detailedFindings: {
                fieldsWithSlashes: this.specialCharacterAnalysis.filter(a => a.hasSlash),
                fieldsWithParentheses: this.specialCharacterAnalysis.filter(a => a.hasParentheses),
                fieldsWithSpecialPunctuation: this.specialCharacterAnalysis.filter(a => a.hasSpecialPunctuation),
                multiLineFields: this.specialCharacterAnalysis.filter(a => a.isMultiLine)
            }
        };
        fs.writeFileSync(reportPath, JSON.stringify(report, null, 2));
        console.log(`\n📊 Detailed report saved to: ${reportPath}`);
    }
    /**
     * Print console report
     */
    printReport() {
        console.log('\n' + '='.repeat(80));
        console.log('📋 COMPREHENSIVE FIELD VERIFICATION REPORT');
        console.log('='.repeat(80));
        const summary = this.generateSummary();
        console.log('\n📊 SUMMARY:');
        console.log(`   Total Tables Verified: ${summary.totalTables}`);
        console.log(`   Perfect Matches: ${summary.perfectMatches} (${((summary.perfectMatches / summary.totalTables) * 100).toFixed(1)}%)`);
        console.log(`   Partial Matches: ${summary.partialMatches} (${((summary.partialMatches / summary.totalTables) * 100).toFixed(1)}%)`);
        console.log(`   Failures: ${summary.failures} (${((summary.failures / summary.totalTables) * 100).toFixed(1)}%)`);
        console.log(`   Overall Match Percentage: ${summary.overallMatchPercentage}%`);
        console.log('\n🔍 DETAILED RESULTS:');
        // Group results by status
        const byStatus = {
            MATCH: this.results.filter(r => r.status === 'MATCH'),
            MISSING_FIELDS: this.results.filter(r => r.status === 'MISSING_FIELDS'),
            EXTRA_FIELDS: this.results.filter(r => r.status === 'EXTRA_FIELDS'),
            CHARACTER_DIFFERENCES: this.results.filter(r => r.status === 'CHARACTER_DIFFERENCES'),
            ERROR: this.results.filter(r => r.status === 'ERROR')
        };
        // Print perfect matches
        if (byStatus.MATCH.length > 0) {
            console.log(`\n✅ PERFECT MATCHES (${byStatus.MATCH.length}):`);
            byStatus.MATCH.forEach(result => {
                console.log(`   ${result.tableName} - ${result.exactMatches.length} fields`);
            });
        }
        // Print missing fields
        if (byStatus.MISSING_FIELDS.length > 0) {
            console.log(`\n❌ MISSING FIELDS (${byStatus.MISSING_FIELDS.length}):`);
            byStatus.MISSING_FIELDS.forEach(result => {
                console.log(`   ${result.tableName} - Missing: ${result.missingFields.length} fields`);
                result.missingFields.forEach(field => {
                    console.log(`     • "${field}"`);
                });
            });
        }
        // Print extra fields
        if (byStatus.EXTRA_FIELDS.length > 0) {
            console.log(`\n⚠️  EXTRA FIELDS (${byStatus.EXTRA_FIELDS.length}):`);
            byStatus.EXTRA_FIELDS.forEach(result => {
                console.log(`   ${result.tableName} - Extra: ${result.extraFields.length} fields`);
                result.extraFields.forEach(field => {
                    console.log(`     • "${field}"`);
                });
            });
        }
        // Print character differences
        if (byStatus.CHARACTER_DIFFERENCES.length > 0) {
            console.log(`\n🔍 CHARACTER DIFFERENCES (${byStatus.CHARACTER_DIFFERENCES.length}):`);
            byStatus.CHARACTER_DIFFERENCES.forEach(result => {
                console.log(`   ${result.tableName}:`);
                result.characterDifferences.forEach(diff => {
                    console.log(`     Excel: "${diff.excel}"`);
                    console.log(`     Database: "${diff.database}"`);
                    console.log(`     Difference: ${diff.difference}`);
                    console.log('');
                });
            });
        }
        // Print errors
        if (byStatus.ERROR.length > 0) {
            console.log(`\n💥 ERRORS (${byStatus.ERROR.length}):`);
            byStatus.ERROR.forEach(result => {
                console.log(`   ${result.tableName}: ${result.errorMessage}`);
            });
        }
        // Print special character analysis
        if (this.specialCharacterAnalysis.length > 0) {
            console.log('\n🔤 SPECIAL CHARACTER ANALYSIS:');
            const withSlashes = this.specialCharacterAnalysis.filter(a => a.hasSlash);
            if (withSlashes.length > 0) {
                console.log(`\n   Fields with slashes (${withSlashes.length}):`);
                withSlashes.forEach(analysis => {
                    console.log(`     • "${analysis.field}"`);
                });
            }
            const withParentheses = this.specialCharacterAnalysis.filter(a => a.hasParentheses);
            if (withParentheses.length > 0) {
                console.log(`\n   Fields with parentheses (${withParentheses.length}):`);
                withParentheses.forEach(analysis => {
                    console.log(`     • "${analysis.field}"`);
                });
            }
            const withSpecialPunctuation = this.specialCharacterAnalysis.filter(a => a.hasSpecialPunctuation);
            if (withSpecialPunctuation.length > 0) {
                console.log(`\n   Fields with special punctuation (${withSpecialPunctuation.length}):`);
                withSpecialPunctuation.forEach(analysis => {
                    console.log(`     • "${analysis.field}" - Characters: ${analysis.specialCharacters.join(', ')}`);
                });
            }
        }
        console.log('\n' + '='.repeat(80));
        console.log('✅ VERIFICATION COMPLETE');
        console.log('='.repeat(80));
    }
    /**
     * Close database connection
     */
    close() {
        this.db.close();
    }
}
exports.ComprehensiveFieldVerifier = ComprehensiveFieldVerifier;
// Main execution function
async function main() {
    const verifier = new ComprehensiveFieldVerifier();
    try {
        console.log('🚀 Starting comprehensive field verification...');
        const results = await verifier.verifyAllFieldMappings();
        // Print console report
        verifier.printReport();
        // Check for critical issues
        const criticalIssues = results.results.filter(r => r.status === 'ERROR' || r.matchPercentage < 50);
        if (criticalIssues.length > 0) {
            console.log(`\n⚠️  CRITICAL ISSUES FOUND: ${criticalIssues.length} tables have serious problems`);
            process.exit(1);
        }
        else {
            console.log('\n✅ All field mappings verified successfully!');
            process.exit(0);
        }
    }
    catch (error) {
        console.error('💥 Fatal error during verification:', error);
        process.exit(1);
    }
    finally {
        verifier.close();
    }
}
// Run if called directly
if (require.main === module) {
    main().catch(console.error);
}
